﻿using com.nm.library.Helper;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;

namespace com.nm.library.Excels
{
    /// <summary>
    /// NPOI操作EXECL帮助类
    /// </summary>
    public static class NPOIHelper
    {
        /// <summary>
        /// EXECL最大列宽
        /// </summary>
        public static readonly int MAX_COLUMN_WIDTH = 100 * 256;

        /// <summary>
        /// 生成EXECL文件，通过读取DataTable和列头映射信息
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="ColumnInfoList">列字段映射信息</param>
        /// <returns>文件流</returns>
        public static MemoryStream Export(DataTable dt, List<ColumnInfo> ColumnInfoList)
        {
            if (dt == null || ColumnInfoList == null)
            {
                throw new ArgumentNullException();
            }
            var rowHeight = 20;
            //每个标签页最多行数
            var sheetRow = 65536;
            var workbook = new HSSFWorkbook();

            #region 文本样式

            var centerStyle = workbook.CreateCellStyle();
            centerStyle.VerticalAlignment = VerticalAlignment.Center;
            centerStyle.Alignment = HorizontalAlignment.Center;

            var leftStyle = workbook.CreateCellStyle();
            leftStyle.VerticalAlignment = VerticalAlignment.Center;
            leftStyle.Alignment = HorizontalAlignment.Left;

            var rightStyle = workbook.CreateCellStyle();
            rightStyle.VerticalAlignment = VerticalAlignment.Center;
            rightStyle.Alignment = HorizontalAlignment.Right;

            #endregion 文本样式

            #region 寻找列头和DataTable之间映射关系

            foreach (DataColumn col in dt.Columns)
            {
                var info = ColumnInfoList.FirstOrDefault(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                if (info == null) continue;
                switch (info.Align.ToLower())
                {
                    case "left":
                        info.Style = leftStyle;
                        break;

                    case "center":
                        info.Style = centerStyle;
                        break;

                    case "right":
                        info.Style = rightStyle;
                        break;
                }
                info.IsMapDT = true;
            }

            #endregion 寻找列头和DataTable之间映射关系

            int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / 65526);
            //最多生成3个标签页的数据
            //sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
            for (var sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
            {
                var sheet = workbook.CreateSheet();
                sheet.CreateFreezePane(0, 1, 0, 1);
                //输出表头
                var headerRow = sheet.CreateRow(0);
                //设置行高
                headerRow.HeightInPoints = rowHeight;
                //首行样式
                var headerStyle = workbook.CreateCellStyle();
                headerStyle.FillPattern = FillPattern.SolidForeground;
                headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                var font = workbook.CreateFont();
                font.Boldweight = short.MaxValue;
                headerStyle.SetFont(font);
                headerStyle.VerticalAlignment = VerticalAlignment.Center; ;
                headerStyle.Alignment = HorizontalAlignment.Center;

                //输出表头信息 并设置表头样式
                var i = 0;
                ICell cell = null;
                foreach (var data in ColumnInfoList)
                {
                    cell = headerRow.CreateCell(i);
                    cell.SetCellValue(data.Header.Trim());
                    cell.CellStyle = headerStyle;
                    i++;
                }

                //开始循环所有行
                var iRow = 1;
                var startRow = sheetIndex * (sheetRow - 10);
                var endRow = (sheetIndex + 1) * (sheetRow - 10);
                endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;

                for (var rowIndex = startRow; rowIndex < endRow; rowIndex++)
                {
                    var row = sheet.CreateRow(iRow);
                    row.HeightInPoints = rowHeight;
                    i = 0;
                    foreach (var item in ColumnInfoList)
                    {
                        cell = row.CreateCell(i);
                        if (item.IsMapDT)
                        {
                            var cellValue = dt.Rows[rowIndex][item.Field];
                            cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
                            cell.CellStyle = item.Style;
                        }
                        i++;
                    }
                    iRow++;
                }
                //自适应列宽度
                for (var j = 0; j < ColumnInfoList.Count; j++)
                {
                    sheet.AutoSizeColumn(j);
                    var width = sheet.GetColumnWidth(j) + 2560;
                    sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                }
            }
            var ms = new MemoryStream();
            workbook.Write(ms);
            return ms;
        }

        /// <summary>
        /// 获取基本数据生成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="workbook"></param>
        /// <param name="ColumnInfoList"></param>
        /// <returns></returns>
        public static MemoryStream Export(DataTable dt, IWorkbook workbook, List<ColumnInfo> ColumnInfoList)
        {
            if (dt == null || ColumnInfoList == null)
            {
                throw new ArgumentNullException();
            }
            var rowHeight = 20;
            //每个标签页最多行数
            var sheetRow = 65536;

            //文本样式
            var centerStyle = workbook.CreateCellStyle();
            centerStyle.VerticalAlignment = VerticalAlignment.Center;
            centerStyle.Alignment = HorizontalAlignment.Center;

            var leftStyle = workbook.CreateCellStyle();
            leftStyle.VerticalAlignment = VerticalAlignment.Center;
            leftStyle.Alignment = HorizontalAlignment.Left;

            var rightStyle = workbook.CreateCellStyle();
            rightStyle.VerticalAlignment = VerticalAlignment.Center;
            rightStyle.Alignment = HorizontalAlignment.Right;

            //寻找列头和DataTable之间映射关系
            foreach (DataColumn col in dt.Columns)
            {
                var info = ColumnInfoList.FirstOrDefault(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                if (info == null) continue;
                switch (info.Align.ToLower())
                {
                    case "left":
                        info.Style = leftStyle;
                        break;

                    case "center":
                        info.Style = centerStyle;
                        break;

                    case "right":
                        info.Style = rightStyle;
                        break;
                }
                info.IsMapDT = true;
            }
            int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / 65526);
            //最多生成5个标签页的数据
            //sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
            for (var sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
            {
                var sheet = workbook.GetSheetAt(sheetIndex);
                sheet.CreateFreezePane(0, 1, 0, 1);
                var i = 0;
                ICell cell;
                //开始循环所有行
                var iRow = 6;
                var startRow = sheetIndex * (sheetRow - 10);
                var endRow = (sheetIndex + 1) * (sheetRow - 10);
                endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;

                for (var rowIndex = startRow; rowIndex < endRow; rowIndex++)
                {
                    var row = sheet.CreateRow(iRow);
                    row.HeightInPoints = rowHeight;
                    i = 0;
                    foreach (var item in ColumnInfoList)
                    {
                        cell = row.CreateCell(i);
                        if (item.IsImage)
                        {
                            var height = PicToExcel(dt.Rows[rowIndex][item.Field].ToString(), workbook, sheet, iRow, i, row);
                            row.HeightInPoints = height;
                        }
                        else
                        {
                            if (item.IsMapDT)
                            {
                                var cellValue = dt.Rows[rowIndex][item.Field];
                                cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
                                cell.CellStyle = item.Style;
                            }
                        }
                        i++;
                    }
                    iRow++;
                }
                //自适应列宽度
                //for (var j = 0; j < ColumnInfoList.Count; j++)
                //{
                //    sheet.AutoSizeColumn(j);
                //    var width = sheet.GetColumnWidth(j) + 2560;
                //    sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                //}
            }
            var ms = new MemoryStream();
            workbook.Write(ms);
            return ms;
        }

        /// <summary>
        /// 生成EXECL文件，通过读取DataTable和列头映射信息
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="ColumnInfoList">列字段映射信息</param>
        /// <returns>文件流</returns>
        public static string ExportToDB(DataTable dt, List<ColumnInfo> ColumnInfoList, string FileName, int num, Dictionary<string, List<string>> dict)
        {
            try
            {
                if (dt == null || ColumnInfoList == null)
                {
                    throw new ArgumentNullException();
                }
                var time = DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒fff");
                var strPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;//获取或设置包含该应用程序的目录的名称。
                if (!Directory.Exists(strPath + "upfile"))
                {
                    Directory.CreateDirectory(strPath + "upfile"); //不存在就创建文件夹 }
                }
                if (!Directory.Exists(strPath + "upfile\\download"))
                {
                    Directory.CreateDirectory(strPath + "upfile\\download"); //不存在就创建文件夹 }
                }
                var dyInfo = new DirectoryInfo(strPath + "upfile\\download");
                //获取文件夹下所有的文件
                foreach (FileInfo feInfo in dyInfo.GetFiles())
                {
                    //判断文件日期是否小于2小时，是则删除
                    if (feInfo.CreationTime < DateTime.Now.AddHours(-1))
                        feInfo.Delete();
                }
                var rowHeight = 20;
                //每个标签页最多行数
                var sheetRow = 65536;
                var workbook = new HSSFWorkbook();

                #region 文本样式

                var centerStyle = workbook.CreateCellStyle();
                centerStyle.VerticalAlignment = VerticalAlignment.Center;
                centerStyle.Alignment = HorizontalAlignment.Center;

                var leftStyle = workbook.CreateCellStyle();
                leftStyle.VerticalAlignment = VerticalAlignment.Center;
                leftStyle.Alignment = HorizontalAlignment.Left;

                var rightStyle = workbook.CreateCellStyle();
                rightStyle.VerticalAlignment = VerticalAlignment.Center;
                rightStyle.Alignment = HorizontalAlignment.Right;

                #endregion 文本样式

                #region 寻找列头和DataTable之间映射关系

                foreach (DataColumn col in dt.Columns)
                {
                    var info =
                        ColumnInfoList.FirstOrDefault(
                            e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                    if (info == null) continue;
                    switch (info.Align.ToLower())
                    {
                        case "left":
                            info.Style = leftStyle;
                            break;

                        case "center":
                            info.Style = centerStyle;
                            break;

                        case "right":
                            info.Style = rightStyle;
                            break;
                    }
                    info.IsMapDT = true;
                }

                #endregion 寻找列头和DataTable之间映射关系

                int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / 65526);
                //最多生成3个标签页的数据
                //sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
                for (var sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
                {
                    var sheet = workbook.CreateSheet();
                    sheet.CreateFreezePane(0, 1, 0, 1);
                    //输出表头
                    var headerRow = sheet.CreateRow(0);
                    //设置行高
                    headerRow.HeightInPoints = rowHeight;
                    //首行样式
                    var headerStyle = workbook.CreateCellStyle();
                    headerStyle.FillPattern = FillPattern.SolidForeground;
                    headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                    var font = workbook.CreateFont();
                    font.Boldweight = short.MaxValue;
                    headerStyle.SetFont(font);
                    headerStyle.VerticalAlignment = VerticalAlignment.Center;
                    ;
                    headerStyle.Alignment = HorizontalAlignment.Center;

                    //输出表头信息 并设置表头样式
                    var i = 0;
                    ICell cell = null;
                    foreach (var data in ColumnInfoList)
                    {
                        cell = headerRow.CreateCell(i);
                        cell.SetCellValue(data.Header.Trim());
                        cell.CellStyle = headerStyle;
                        i++;
                    }

                    //开始循环所有行
                    var iRow = 1;
                    var startRow = sheetIndex * (sheetRow - 10);
                    var endRow = (sheetIndex + 1) * (sheetRow - 10);
                    endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;

                    for (var k = 0; k < ColumnInfoList.Count; k++)
                    {
                        switch (ColumnInfoList[k].Field)
                        {
                            case "Yearv":
                                sheet = SetHSSFValidation(sheet, dict["Yearv"].ToArray(), iRow, k);
                                break;

                            case "Monthv":
                                sheet = SetHSSFValidation(sheet, dict["Monthv"].ToArray(), iRow, k);
                                break;

                            case "ParentAreaName":
                                sheet = SetHSSFValidation(sheet, dict["ParentAreaName"].ToArray(), iRow, k);
                                break;

                            case "SeasonName":
                                sheet = SetHSSFValidation(sheet, dict["SeasonName"].ToArray(), iRow, k);
                                break;

                            case "KindName":
                                sheet = SetHSSFValidation(sheet, dict["KindName"].ToArray(), iRow, k);
                                break;

                            case "SeriesName":
                                sheet = SetHSSFValidation(sheet, dict["SeriesName"].ToArray(), iRow, k);
                                break;

                            default: break;
                        }
                    }

                    for (var rowIndex = startRow; rowIndex < endRow; rowIndex++)
                    {
                        var row = sheet.CreateRow(iRow);
                        row.HeightInPoints = rowHeight;
                        i = 0;
                        foreach (var item in ColumnInfoList)
                        {
                            cell = row.CreateCell(i);
                            if (item.IsMapDT)
                            {
                                var cellValue = dt.Rows[rowIndex][item.Field];
                                cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
                                cell.CellStyle = item.Style;
                            }
                            i++;
                        }
                        iRow++;
                    }
                    //自适应列宽度
                    for (var j = 0; j < ColumnInfoList.Count; j++)
                    {
                        sheet.AutoSizeColumn(j);
                        var width = sheet.GetColumnWidth(j) + 2560;
                        sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                    }
                }
                var filePath = strPath + $"upfile\\download\\{FileName}_{time}_{num}.xls";
                var fs = new FileStream(filePath, FileMode.Create);
                workbook.Write(fs);
                fs.Close();
                return filePath;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 获取基本数据生成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="workbook"></param>
        /// <param name="ColumnInfoList"></param>
        /// <returns></returns>
        public static string ExportToDB(DataTable dt, IWorkbook workbook, List<ColumnInfo> ColumnInfoList, string FileName, int num, Dictionary<string, List<string>> dict)
        {
            try
            {
                if (dt == null || ColumnInfoList == null)
                {
                    throw new ArgumentNullException();
                }
                var time = DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒fff");
                var strPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;//获取或设置包含该应用程序的目录的名称。
                if (!Directory.Exists(strPath + "upfile"))
                {
                    Directory.CreateDirectory(strPath + "upfile"); //不存在就创建文件夹 }
                }
                if (!Directory.Exists(strPath + "upfile\\download"))
                {
                    Directory.CreateDirectory(strPath + "upfile\\download"); //不存在就创建文件夹 }
                }
                var dyInfo = new DirectoryInfo(strPath + "upfile\\download");
                //获取文件夹下所有的文件
                foreach (FileInfo feInfo in dyInfo.GetFiles())
                {
                    //判断文件日期是否小于2小时，是则删除
                    if (feInfo.CreationTime < DateTime.Now.AddHours(-1))
                        feInfo.Delete();
                }
                var rowHeight = 20;
                //每个标签页最多行数
                var sheetRow = 65536;

                //文本样式
                var centerStyle = workbook.CreateCellStyle();
                centerStyle.VerticalAlignment = VerticalAlignment.Center;
                centerStyle.Alignment = HorizontalAlignment.Center;

                var leftStyle = workbook.CreateCellStyle();
                leftStyle.VerticalAlignment = VerticalAlignment.Center;
                leftStyle.Alignment = HorizontalAlignment.Left;

                var rightStyle = workbook.CreateCellStyle();
                rightStyle.VerticalAlignment = VerticalAlignment.Center;
                rightStyle.Alignment = HorizontalAlignment.Right;

                //寻找列头和DataTable之间映射关系
                foreach (DataColumn col in dt.Columns)
                {
                    var info =
                        ColumnInfoList.FirstOrDefault(
                            e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                    if (info == null) continue;
                    switch (info.Align.ToLower())
                    {
                        case "left":
                            info.Style = leftStyle;
                            break;

                        case "center":
                            info.Style = centerStyle;
                            break;

                        case "right":
                            info.Style = rightStyle;
                            break;
                    }
                    info.IsMapDT = true;
                }
                int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / 65526);
                //最多生成5个标签页的数据
                //sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
                for (var sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
                {
                    var sheet = workbook.GetSheetAt(sheetIndex);
                    sheet.CreateFreezePane(0, 1, 0, 1);
                    var i = 0;
                    ICell cell;
                    //开始循环所有行
                    var iRow = 6;
                    var startRow = sheetIndex * (sheetRow - 10);
                    var endRow = (sheetIndex + 1) * (sheetRow - 10);
                    endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;

                    for (var k = 0; k < ColumnInfoList.Count; k++)
                    {
                        switch (ColumnInfoList[k].Field)
                        {
                            case "Yearv":
                                sheet = SetHSSFValidation(sheet, dict["Yearv"].ToArray(), iRow, k);
                                break;

                            case "Monthv":
                                sheet = SetHSSFValidation(sheet, dict["Monthv"].ToArray(), iRow, k);
                                break;

                            case "ParentAreaName":
                                sheet = SetHSSFValidation(sheet, dict["ParentAreaName"].ToArray(), iRow, k);
                                break;

                            case "SeasonName":
                                sheet = SetHSSFValidation(sheet, dict["SeasonName"].ToArray(), iRow, k);
                                break;

                            case "KindName":
                                sheet = SetHSSFValidation(sheet, dict["KindName"].ToArray(), iRow, k);
                                break;

                            case "SeriesName":
                                sheet = SetHSSFValidation(sheet, dict["SeriesName"].ToArray(), iRow, k);
                                break;

                            default: break;
                        }
                    }

                    for (var rowIndex = startRow; rowIndex < endRow; rowIndex++)
                    {
                        var row = sheet.CreateRow(iRow);
                        row.HeightInPoints = rowHeight;
                        i = 0;
                        foreach (var item in ColumnInfoList)
                        {
                            cell = row.CreateCell(i);
                            if (item.IsImage)
                            {
                                var height = PicToExcel(dt.Rows[rowIndex][item.Field].ToString(), workbook, sheet, iRow,
                                    i, row);
                                row.HeightInPoints = height;
                            }
                            else
                            {
                                if (item.IsMapDT)
                                {
                                    var cellValue = dt.Rows[rowIndex][item.Field];
                                    cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
                                    cell.CellStyle = item.Style;
                                }
                            }
                            i++;
                        }
                        iRow++;
                    }
                    //自适应列宽度
                    //for (var j = 0; j < ColumnInfoList.Count; j++)
                    //{
                    //    sheet.AutoSizeColumn(j);
                    //    var width = sheet.GetColumnWidth(j) + 2560;
                    //    sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                    //}
                }
                var filePath = strPath + $"upfile\\download\\{FileName}_{time}_{num}.xls";
                var fs = new FileStream(filePath, FileMode.Create);
                workbook.Write(fs);
                fs.Close();
                return filePath;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 获取基本数据生成Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="workbook"></param>
        /// <param name="ColumnInfoList"></param>
        /// <returns></returns>
        public static string ExportAll(IList<DataTable> dtList, IList<List<ColumnInfo>> ColumnInfoListStr, IList<string> FileName, int num, Dictionary<string, List<string>> dict)
        {
            try
            {
                if (!dtList.Any() || !ColumnInfoListStr.Any())
                {
                    throw new ArgumentNullException();
                }
                var time = DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒fff");
                var strPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;//获取或设置包含该应用程序的目录的名称。
                if (!Directory.Exists(strPath + "upfile"))
                {
                    Directory.CreateDirectory(strPath + "upfile"); //不存在就创建文件夹 }
                }
                if (!Directory.Exists(strPath + "upfile\\download"))
                {
                    Directory.CreateDirectory(strPath + "upfile\\download"); //不存在就创建文件夹 }
                }
                var dyInfo = new DirectoryInfo(strPath + "upfile\\download");
                //获取文件夹下所有的文件
                foreach (FileInfo feInfo in dyInfo.GetFiles())
                {
                    //判断文件日期是否小于2小时，是则删除
                    if (feInfo.CreationTime < DateTime.Now.AddHours(-1))
                        feInfo.Delete();
                }
                var rowHeight = 20;
                //每个标签页最多行数
                var sheetRow = 65536;
                var workbook = new HSSFWorkbook();

                #region 文本样式

                var centerStyle = workbook.CreateCellStyle();
                centerStyle.VerticalAlignment = VerticalAlignment.Center;
                centerStyle.Alignment = HorizontalAlignment.Center;

                var leftStyle = workbook.CreateCellStyle();
                leftStyle.VerticalAlignment = VerticalAlignment.Center;
                leftStyle.Alignment = HorizontalAlignment.Left;

                var rightStyle = workbook.CreateCellStyle();
                rightStyle.VerticalAlignment = VerticalAlignment.Center;
                rightStyle.Alignment = HorizontalAlignment.Right;

                #endregion 文本样式

                var index = 0;
                for (var i = 0; i < dtList.Count(); i++)
                {

                    #region 寻找列头和DataTable之间映射关系

                    foreach (DataColumn col in dtList[i].Columns)
                    {
                        var info =
                            ColumnInfoListStr[i].FirstOrDefault(
                                e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                        if (info == null) continue;
                        switch (info.Align.ToLower())
                        {
                            case "left":
                                info.Style = leftStyle;
                                break;

                            case "center":
                                info.Style = centerStyle;
                                break;

                            case "right":
                                info.Style = rightStyle;
                                break;
                        }
                        info.IsMapDT = true;
                    }

                    #endregion 寻找列头和DataTable之间映射关系

                    int sheetNum = (int) Math.Ceiling(dtList[i].Rows.Count * 1.0 / 65526) + index;
                    //最多生成3个标签页的数据
                    //sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
                    for (var sheetIndex = 0 + index; sheetIndex < sheetNum; sheetIndex++)
                    {
                        var sheet = workbook.CreateSheet();
                        workbook.SetSheetName(sheetIndex, FileName[i]);
                        sheet.CreateFreezePane(0, 1, 0, 1);
                        //输出表头
                        var headerRow = sheet.CreateRow(0);
                        //设置行高
                        headerRow.HeightInPoints = rowHeight;
                        //首行样式
                        var headerStyle = workbook.CreateCellStyle();
                        headerStyle.FillPattern = FillPattern.SolidForeground;
                        headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
                        var font = workbook.CreateFont();
                        font.Boldweight = short.MaxValue;
                        headerStyle.SetFont(font);
                        headerStyle.VerticalAlignment = VerticalAlignment.Center;
                        ;
                        headerStyle.Alignment = HorizontalAlignment.Center;

                        //输出表头信息 并设置表头样式
                        var z = 0;
                        ICell cell = null;
                        foreach (var data in ColumnInfoListStr[i])
                        {
                            cell = headerRow.CreateCell(z);
                            cell.SetCellValue(data.Header.Trim());
                            cell.CellStyle = headerStyle;
                            z++;
                        }

                        //开始循环所有行
                        var iRow = 1;
                        var startRow = 0;
                        var endRow = sheetRow - 10;
                        endRow = endRow <= dtList[i].Rows.Count ? endRow : dtList[i].Rows.Count;

                        for (var rowIndex = startRow; rowIndex < endRow; rowIndex++)
                        {
                            var row = sheet.CreateRow(iRow);
                            row.HeightInPoints = rowHeight;
                            z = 0;
                            foreach (var item in ColumnInfoListStr[i])
                            {
                                cell = row.CreateCell(z);
                                if (item.IsMapDT)
                                {
                                    var cellValue = dtList[i].Rows[rowIndex][item.Field];
                                    cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
                                    cell.CellStyle = item.Style;
                                }
                                z++;
                            }
                            iRow++;
                        }

                        //自适应列宽度
                        for (var j = 0; j < ColumnInfoListStr[i].Count; j++)
                        {
                            sheet.AutoSizeColumn(j);
                            var width = sheet.GetColumnWidth(j) + 2560;
                            sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                        }
                    }
                    index = sheetNum;
                }
                var filePath = strPath + $"upfile\\download\\{FileName[0]}_{time}_{num}.xls";
                var fs = new FileStream(filePath, FileMode.Create);
                workbook.Write(fs);
                fs.Close();
                return filePath;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 获取基本数据生成错误信息Excel
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="workbook"></param>
        /// <param name="ColumnInfoList"></param>
        /// <returns></returns>
        public static string ExportErrorToDB(DataTable dt, IWorkbook workbook, List<ColumnInfo> ColumnInfoList, string FileName, int num)
        {
            try
            {
                if (dt == null || ColumnInfoList == null)
                {
                    throw new ArgumentNullException();
                }
                var time = DateTime.Now.ToString("yyyy年MM月dd日HH时mm分ss秒fff");
                var strPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;//获取或设置包含该应用程序的目录的名称。
                if (!Directory.Exists(strPath + "TempFile"))
                {
                    Directory.CreateDirectory(strPath + "TempFile"); //不存在就创建文件夹 }
                }
                if (!Directory.Exists(strPath + "TempFile\\ErrorExcel"))
                {
                    Directory.CreateDirectory(strPath + "TempFile\\ErrorExcel"); //不存在就创建文件夹 }
                }
                var dyInfo = new DirectoryInfo(strPath + "TempFile\\ErrorExcel");
                //获取文件夹下所有的文件
                foreach (FileInfo feInfo in dyInfo.GetFiles())
                {
                    //判断文件日期是否小于2小时，是则删除
                    if (feInfo.CreationTime < DateTime.Now.AddHours(-1))
                        feInfo.Delete();
                }
                var rowHeight = 20;
                //每个标签页最多行数
                var sheetRow = 65536;

                //文本样式
                var centerStyle = workbook.CreateCellStyle();
                centerStyle.VerticalAlignment = VerticalAlignment.Center;
                centerStyle.Alignment = HorizontalAlignment.Center;

                var leftStyle = workbook.CreateCellStyle();
                leftStyle.VerticalAlignment = VerticalAlignment.Center;
                leftStyle.Alignment = HorizontalAlignment.Left;

                var rightStyle = workbook.CreateCellStyle();
                rightStyle.VerticalAlignment = VerticalAlignment.Center;
                rightStyle.Alignment = HorizontalAlignment.Right;

                //寻找列头和DataTable之间映射关系
                foreach (DataColumn col in dt.Columns)
                {
                    var info =
                        ColumnInfoList.FirstOrDefault(
                            e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                    if (info == null) continue;
                    switch (info.Align.ToLower())
                    {
                        case "left":
                            info.Style = leftStyle;
                            break;

                        case "center":
                            info.Style = centerStyle;
                            break;

                        case "right":
                            info.Style = rightStyle;
                            break;
                    }
                    info.IsMapDT = true;
                }
                int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / 65526);
                //最多生成5个标签页的数据
                //sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
                for (var sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
                {
                    var sheet = workbook.GetSheetAt(sheetIndex);
                    sheet.CreateFreezePane(0, 1, 0, 1);
                    var i = 0;
                    ICell cell;
                    //开始循环所有行
                    var iRow = 6;
                    var startRow = sheetIndex * (sheetRow - 10);
                    var endRow = (sheetIndex + 1) * (sheetRow - 10);
                    endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;

                    for (var rowIndex = startRow; rowIndex < endRow; rowIndex++)
                    {
                        var row = sheet.CreateRow(iRow);
                        row.HeightInPoints = rowHeight;
                        i = 0;
                        foreach (var item in ColumnInfoList)
                        {
                            cell = row.CreateCell(i);
                            if (item.IsImage)
                            {
                                var height = PicToExcel(dt.Rows[rowIndex][item.Field].ToString(), workbook, sheet, iRow,
                                    i, row);
                                row.HeightInPoints = height;
                            }
                            else
                            {
                                if (item.IsMapDT)
                                {
                                    var cellValue = dt.Rows[rowIndex][item.Field];
                                    cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
                                    cell.CellStyle = item.Style;
                                }
                            }
                            i++;
                        }
                        iRow++;
                    }
                    //自适应列宽度
                    //for (var j = 0; j < ColumnInfoList.Count; j++)
                    //{
                    //    sheet.AutoSizeColumn(j);
                    //    var width = sheet.GetColumnWidth(j) + 2560;
                    //    sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                    //}
                }
                var filePath = $"\\TempFile\\ErrorExcel\\{FileName}_{time}_{num}.xls";
                var fs = new FileStream(strPath + filePath, FileMode.Create);
                workbook.Write(fs);
                fs.Close();
                return filePath;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 获取第一个Sheet
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns>Sheet</returns>
        public static ISheet GetFirstSheet(string filePath)
        {
            using (Stream stream = new MemoryStream(File.ReadAllBytes(filePath)))
            {
                IWorkbook workbook = new HSSFWorkbook(stream);
                if (workbook.NumberOfSheets > 0)
                {
                    return workbook.GetSheetAt(0);
                }
            }
            return null;
        }

        #region "Excel模版数据读取相关"

        /// <summary>
        /// 通过输入流初始化workbook
        /// </summary>
        /// <param name="ins">输入流</param>
        /// <param name="fileName"></param>
        /// <returns>workbook对象</returns>
        public static IWorkbook InitWorkBook(Stream ins, string fileName)
        {
            if (!string.IsNullOrWhiteSpace(fileName))
            {
                var str = fileName.Split('.');
                if (str[1] == "xlsx")
                    return new XSSFWorkbook(ins);
            }
            return new HSSFWorkbook(ins);
        }

        /// <summary>
        /// 从excel第一个sheet中读取数据
        /// </summary>
        /// <param name="ins">输入流</param>
        /// <param name="headRowIndex">标题行索引 默认为第6行</param>
        /// <param name="fSheet">第一个sheet</param>
        /// <returns>DataTable</returns>
        public static DataTable GetDataFromExcel(Stream ins, string fileName, out ISheet fSheet, int headRowIndex = 5)
        {
            var workbook = InitWorkBook(ins, fileName);
            fSheet = null;
            var dt = new DataTable();
            if (workbook.NumberOfSheets <= 0) return dt;
            fSheet = workbook.GetSheetAt(0);
            if (fSheet.LastRowNum < headRowIndex)
            {
                throw new ArgumentException("Excel模版错误,标题行索引大于总行数");
            }
            //读取标题行
            ICell cell;
            var row = fSheet.GetRow(headRowIndex);
            for (int i = 0, length = row.LastCellNum; i < length; i++)
            {
                cell = row.GetCell(i);
                if (cell == null)
                {
                    continue;
                }
                var objColumnName = GetCellVale(cell);
                dt.Columns.Add(objColumnName?.ToString() ?? "");
            }
            //读取数据行
            var columnCount = dt.Columns.Count;
            //2017.05.11修复bug：LastRowNum获取的是行索引,索引是从0开始的,所以不能直接当长度length用,必须+1，否则会丢失最后一条数据，
            //                   不想+1可以不用LastRowNum用PhysicalNumberOfRows,后者获取的本身就是行的数量，不存在索引问题
            for (int i = headRowIndex + 1; i < fSheet.LastRowNum + 1; i++)
            {
                row = fSheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }
                var entityValues = new object[columnCount];
                //用于判断是否为空行
                var isHasData = false;
                var dataColumnLength = row.LastCellNum < columnCount ? row.LastCellNum : columnCount;
                for (var j = 0; j < dataColumnLength; j++)
                {
                    cell = row.GetCell(j);
                    if (cell == null)
                    {
                        continue;
                    }
                    entityValues[j] = GetCellVale(cell);
                    if (!isHasData && j < columnCount && entityValues[j] != null)
                    {
                        isHasData = true;
                    }
                }
                if (isHasData)
                {
                    dt.Rows.Add(entityValues);
                }
            }
            return dt;
        }

        /// <summary>
        /// 设置excel模版错误信息
        /// </summary>
        /// <param name="sheet">数据标签</param>
        /// <param name="rowindex">错误信息显示行</param>
        /// <param name="msg">错误信息</param>
        public static void SetTemplateErrorMsg(ISheet sheet, int rowindex, string msg)
        {
            var row = sheet.GetRow(rowindex);
            row = sheet.CreateRow(rowindex);
            if (row == null || string.IsNullOrEmpty(msg)) return;
            sheet.AddMergedRegion(new CellRangeAddress(rowindex, rowindex, 0, row.LastCellNum));

            var cell = row.GetCell(0) ?? row.CreateCell(0);
            var cellStyle = sheet.Workbook.CreateCellStyle();
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Left;
            var font = sheet.Workbook.CreateFont();
            font.FontHeightInPoints = 12;
            font.Color = HSSFColor.Red.Index;
            cellStyle.SetFont(font);
            cell.CellStyle = cellStyle;
            cell.SetCellValue(msg);
        }

        /// <summary>
        /// 获取数据行的错误信息提示样式
        /// </summary>
        /// <returns>错误数据行样式</returns>
        public static ICellStyle GetErrorCellStyle(IWorkbook wb)
        {
            var cellStyle = wb.CreateCellStyle();
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Left;
            var font = wb.CreateFont();
            //font.FontHeightInPoints = 12;
            font.Color = HSSFColor.Red.Index;
            cellStyle.SetFont(font);
            return cellStyle;
        }

        /// <summary>
        /// 获取标题行的错误信息提示样式
        /// </summary>
        /// <returns>错误标题行样式</returns>
        public static ICellStyle GetErrorHeadCellStyle(IWorkbook wb)
        {
            var cellStyle = wb.CreateCellStyle();
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Center;
            var font = wb.CreateFont();
            font.Boldweight = short.MaxValue;
            font.Color = HSSFColor.Red.Index;
            cellStyle.SetFont(font);
            cellStyle.FillPattern = FillPattern.SolidForeground;
            return cellStyle;
        }

        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>单元格值</returns>
        private static object GetCellVale(ICell cell)
        {
            object obj = null;
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        obj = cell.DateCellValue;
                    }
                    else
                    {
                        obj = cell.NumericCellValue;
                    }
                    break;

                case CellType.String:
                    obj = cell.StringCellValue;
                    break;

                case CellType.Boolean:
                    obj = cell.BooleanCellValue;
                    break;

                case CellType.Formula:
                    obj = cell.CellFormula;
                    break;
            }
            return obj;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="picPath"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        public static int PicToExcel(string picPath, IWorkbook workbook, ISheet sheet, int row, int col, IRow irow)
        {
            var picBytes = new WebClient().DownloadData(picPath);
            //插入图片
            int h = 0;
            int w = 0;
            if (picBytes == null || picBytes.Length <= 0) return 40;
            var ms = new MemoryStream(picBytes);
            var img = Image.FromStream(ms);
            var b = ImageThumbnailHelper.GenerateHighThumbnail(img, out h, out w);
            var pictureIdx = workbook.AddPicture(b, PictureType.JPEG);  //添加图片
            var patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            var anchor = new HSSFClientAnchor(2, 2, w, h, col, row, col, row);
            //图片位置，图片左上角为（col, row）
            var pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
            pict.Resize(); //用图片原始大小来显示
            ms.Dispose();
            ms.Close();
            return h;
        }

        #endregion "Excel模版数据读取相关"

        #region "设置下拉选项"

        /// <summary>
        /// 设置某些列的值只能输入预制的数据,显示下拉框
        /// </summary>
        /// <param name="sheet">要设置的sheet</param>
        /// <param name="textlist">下拉框显示的内容</param>
        /// <param name="firstRow">开始行</param>
        /// <param name="firstCol">开始列</param>
        /// <returns>设置好的sheet</returns>
        public static ISheet SetHSSFValidation(ISheet sheet,
                string[] textlist, int firstRow, int firstCol)
        {
            return SetHSSFValidation(sheet, textlist, firstRow, sheet.LastRowNum, firstCol, firstCol);
        }

        /// <summary>
        /// 设置某些列的值只能输入预制的数据,显示下拉框
        /// </summary>
        /// <param name="sheet">要设置的sheet</param>
        /// <param name="textlist">下拉框显示的内容</param>
        /// <param name="firstRow">开始行</param>
        /// <param name="endRow">结束行</param>
        /// <param name="firstCol">开始列</param>
        /// <param name="endCol">结束列</param>
        /// <returns>设置好的sheet</returns>
        public static ISheet SetHSSFValidation(ISheet sheet,
                string[] textlist, int firstRow, int endRow, int firstCol,
                int endCol)
        {
            var workbook = sheet.Workbook;
            if (endRow > sheet.LastRowNum)
            {
                endRow = sheet.LastRowNum;
            }
            ISheet hidden;
            var hiddenSheetName = "hidden" + sheet.SheetName;
            var hIndex = workbook.GetSheetIndex(hiddenSheetName);
            if (hIndex < 0)
            {
                hidden = workbook.CreateSheet(hiddenSheetName);
                workbook.SetSheetHidden(sheet.Workbook.NumberOfSheets - 1, SheetState.Hidden);
            }
            else
            {
                hidden = workbook.GetSheetAt(hIndex);
            }
            for (int i = 0, length = textlist.Length; i < length; i++)
            {
                var row = hidden.GetRow(i) ?? hidden.CreateRow(i);
                var cell = row.GetCell(firstCol) ?? row.CreateCell(firstCol);
                cell.SetCellValue(textlist[i]);
            }
            // 加载下拉列表内容
            var nameCellKey = hiddenSheetName + firstCol;
            var namedCell = workbook.GetName(nameCellKey);
            if (namedCell == null)
            {
                namedCell = workbook.CreateName();
                namedCell.NameName = nameCellKey;
                namedCell.RefersToFormula = string.Format("{0}!${1}$1:${1}${2}", hiddenSheetName, NumberToChar(firstCol + 1), textlist.Length);
            }
            var constraint = DVConstraint.CreateFormulaListConstraint(nameCellKey);
            // 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列
            var regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
            // 数据有效性对象
            var validation = new HSSFDataValidation(regions, constraint);
            //// 取消弹出错误框
            //validation.ShowErrorBox = false;
            sheet.AddValidationData(validation);
            return sheet;
        }

        #endregion "设置下拉选项"

        #region "私有方法"

        ///
        /// 把1,2,3,...,35,36转换成A,B,C,...,Y,Z
        ///
        /// 要转换成字母的数字（数字范围在闭区间[1,36]）
        ///
        private static string NumberToChar(int number)
        {
            if (1 > number || 36 < number) return "A";
            var num = number + 64;
            var asciiEncoding = new System.Text.ASCIIEncoding();
            var btNumber = new[] { (byte)num };
            return asciiEncoding.GetString(btNumber);
        }

        #endregion "私有方法"
    }

    /// <summary>
    /// NPOI拓展方法
    /// </summary>
    public static class NPOIExtend
    {
        /// <summary>
        /// 获取RGB对应NPOI颜色值
        /// </summary>
        /// <param name="workbook">当前wb</param>
        /// <param name="R"></param>
        /// <param name="G"></param>
        /// <param name="B"></param>
        /// <returns></returns>
        public static short GetXLColour(this HSSFWorkbook workbook, int R, int G, int B)
        {
            short s = 0;
            var XlPalette = workbook.GetCustomPalette();
            var XlColour = XlPalette.FindColor((byte)R, (byte)G, (byte)B);
            if (XlColour == null)
            {
                if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255)
                {
                    //if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64)
                    //{
                    //    NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64;
                    //    NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1;
                    //    XlColour = XlPalette.AddColor((byte)R, (byte)G, (byte)B);
                    //}
                    //else
                    //{
                    //    XlColour = XlPalette.FindSimilarColor((byte)R, (byte)G, (byte)B);
                    //}

                    // s = XlColour.GetIndex();
                }
            }
            else
            {
                // s = XlColour.GetIndex();
            }
            return s;
        }

        /// <summary>
        /// 冻结表格
        /// </summary>
        /// <param name="sheet">sheet</param>
        /// <param name="colCount">冻结的列数</param>
        /// <param name="rowCount">冻结的行数</param>
        /// <param name="startCol">右边区域可见的首列序号，从1开始计算</param>
        /// <param name="startRow">下边区域可见的首行序号，也是从1开始计算</param>
        /// <example>
        /// sheet1.CreateFreezePane(0, 1, 0, 1); 冻结首行
        /// sheet1.CreateFreezePane(1, 0, 1, 0);冻结首列
        /// </example>
        public static void FreezePane(this ISheet sheet, int colCount, int rowCount, int startCol, int startRow)
        {
            sheet.CreateFreezePane(colCount, rowCount, startCol, startRow);
        }
    }
}